package com.ctvit.sms.platform.service;

import com.ctvit.framework.dao.Condition;
import com.ctvit.framework.dao.OrderPart;
import com.ctvit.framework.service.BaseService;
import com.ctvit.sms.platform.domain.MsgCategory;
import com.ctvit.sms.platform.domain.MsgSubCat;
import com.ctvit.sms.platform.domain.SubCateWithUserSubs;
import com.ctvit.sms.platform.domain.UserSubs;

import java.sql.Timestamp;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;

/**
 * Created by LoreYun on 2016/1/13.
 */
public class SubsService extends BaseService {
	/**
	 *
	 */
	public static char GET_UNUSING = '1';
	public static char GET_USING = '2';
	public static char GET_ALL = '3';


	public static char STATUS_ALL = 'X';       //全部
	public static char STATUS_HIDDEN = '0';       //不展示给用户，即用户不可订阅
	public static char STATUS_USER = '1';         //展示给用户，用户可以订阅

	public void doUpdateAbleStatusByUserIDCate(String userID, String categoryID, String status) {
//		status = 0 可用
//		status = 1 不可用
		if (userID == null || userID.equals("") || categoryID == null || categoryID.equals("") || status == null || status.equals("")) {
			System.out.println("doUpdateAbleStatusByUserIDCate 参数不正确： " + userID + " ; " + categoryID + " ; " + status);
			return;
		}
		String str_sql;
		str_sql = "UPDATE t_user_subs " +
				"SET " +
				"    f_is_able = " + status +
				" WHERE" +
				"    f_user_id = '" + userID + "' " +
				"        AND f_msg_sub_cat IN (SELECT " +
				"            f_msg_sub_cat" +
				"        FROM" +
				"            t_msg_sub_cat" +
				"        WHERE" +
				"            f_msg_category = '" + categoryID + "')";
		genericDao.updateBySqlStr(str_sql);
	}

	public void doUpdateOpenStatusByUserIDSubcatID(String userID, String subCateID, String status) {
		//		status = 0 可用
		//		status = 1 不可用
		//先判断t_user_subs表是否有该小类的记录
		//   如果有小类 就更新，
		//   没有小类 就新建一条
		if (userID == null || userID.equals("") || subCateID == null || subCateID.equals("") || status == null || status.equals("")) {
			System.out.println("doUpdateAbleStatusByUserIDCate 参数不正确： " + userID + " ; " + subCateID + " ; " + status);
			return;
		}
//		String str_sql;
//		str_sql = "UPDATE t_user_subs \n" +
//				"SET \n" +
//				"    f_is_open = " + status + "\n" +
//				"WHERE\n" +
//				"    f_user_id = '" + userID + "'\n" +
//				"        AND f_msg_sub_cat = '" + subCateID + " '";
//		genericDao.executeSql(str_sql);
		boolean hasRecord;
		Condition condition = new Condition();
		condition.add("userId", userID);
		condition.add("msgSubCat", subCateID);

		UserSubs userSubs;
		List<UserSubs> list = genericDao.getByCondition(UserSubs.class, condition, null);
		int recordCount = list.size();

		if (recordCount == 0) {             //不存在条目  需要插入一条新纪录
			userSubs = new UserSubs();

			userSubs.setUserId(userID);
			userSubs.setMsgSubCat(subCateID);
			userSubs.setIsOpen(Integer.valueOf(status));
			userSubs.setIsAble(0);                                  //能触发这个function 就证明大类已经打开了 所以 able = 0
			userSubs.setCreateTimestamp(new Timestamp(GregorianCalendar.getInstance().getTimeInMillis()));
			genericDao.insert(userSubs);
		} else {                       //存在条目      更新就好
			userSubs = list.get(0);
			userSubs.setIsOpen(Integer.valueOf(status));

			genericDao.update(userSubs, new String[]{"isOpen"});
		}
	}

	/**
	 * 根据 用户ID、大类 获取小类 和小类下的使用情况
	 *
	 * @param category 大类
	 * @param userID   小类
	 * @param status   类型状态
	 *                 STATUS_ALL    X - 所有类型
	 *                 STATUS_HIDDEN    0 - 不展示给用户，即用户不可订阅
	 *                 STATUS_USER    1 - 展示给用户，用户可以订阅
	 * @return
	 */
	public List<SubCateWithUserSubs> getSubCateByCateUsIDWithSubsed(String category, String userID, char status) {
		String msgCategoryIsUse = "0";
		String userSubsIsOpen = null;
		String userSubsIsAble = null;
		String msgSubCatIsUse = "0";
		List<SubCateWithUserSubs> list = getUserSubs(userID, category, msgCategoryIsUse, userSubsIsOpen, userSubsIsAble, msgSubCatIsUse, status);
		return list;
	}

	/**
	 * 搜索某一大类别下的所有子类别 ，并且某一用户的订阅状态
	 *
	 * @param userId           要搜索的用户ID
	 * @param msgCategory      消息大类代码
	 * @param userSubsIsAble   用户是否订阅 0-订阅 1-未订阅        null - 全部
	 * @param msgSubCatIsUse   小类别是否可用 0-可用 1-不可用     null - 全部
	 * @param msgCategoryIsUse 消息大类是否可用  0-可用 1-不可用        null - 全部
	 * @param status           类型状态
	 *                         STATUS_ALL    X - 所有类型
	 *                         STATUS_HIDDEN    0 - 不展示给用户，即用户不可订阅
	 *                         STATUS_USER    1 - 展示给用户，用户可以订阅
	 * @return
	 */
	public List<SubCateWithUserSubs> getUserSubs(String userId, String msgCategory,
	                                             String msgCategoryIsUse, String userSubsIsOpen, String userSubsIsAble,
	                                             String msgSubCatIsUse, char status) {
		HashMap conditionMap = new HashMap();
		if (userId != null) {
			conditionMap.put("userId", userId);
		}
		if (msgCategory != null) {
			conditionMap.put("msgCategory", msgCategory);
		}
		if (userSubsIsAble != null) {
			conditionMap.put("userSubsIsAble", userSubsIsAble);
		}
		if (userSubsIsOpen != null) {
			conditionMap.put("userSubsIsOpen", userSubsIsOpen);
		}
		if (msgSubCatIsUse != null) {
			conditionMap.put("msgSubCatIsUse", msgSubCatIsUse);
		}
		if (msgCategoryIsUse != null) {
			conditionMap.put("msgCategoryIsUse", msgCategoryIsUse);
		}

		if (status == STATUS_ALL) {
			//因为现在只有2个类别 所以 不进行条件判断
		} else if (status == STATUS_HIDDEN) {       //不展示给用户的有哪些
			conditionMap.put("msgSubCatStatus", 0);       //小类
			conditionMap.put("msgCategoryStatus", 0);       //大类
		} else if (status == STATUS_USER) {
			conditionMap.put("msgSubCatStatus", 1);       //小类
			conditionMap.put("msgCategoryStatus", 1);      //大类
		}

		List<SubCateWithUserSubs> list = genericDao.queryBySqlID("platform.custom.SubCateWithUserSubs", conditionMap);
		return list;
	}

	/**
	 * 根据传入的使用状态，查询消息大类
	 *
	 * @param usingStatus SubsService.GET_UNUSING = 1 代表没使用的，
	 *                    SubsService.GET_USING = 2 代表使用的，
	 *                    SubsService.GET_ALL = 3 代表全部.
	 * @param status      类型状态
	 *                    STATUS_ALL    X - 所有类型
	 *                    STATUS_HIDDEN    0 - 不展示给用户，即用户不可订阅
	 *                    STATUS_USER    1 - 展示给用户，用户可以订阅
	 * @return
	 */
	public List<MsgCategory> getMsgCategoryList(char usingStatus, char status) {
		Condition condition = new Condition();
		setUsingStatusCondition(condition, usingStatus, status);

		OrderPart orders = new OrderPart();
		orders.add("sortIndex", OrderPart.ASC);
		List<MsgCategory> result = genericDao.getByCondition(MsgCategory.class, condition, orders);
		return result;
	}

	/**
	 * 根据消息大类 和使用情况 搜索其下的消息小类
	 *
	 * @param msgCategory 消息大类的JavaBean
	 * @param usingStatus SubsService.GET_UNUSING = 1 代表没使用的，
	 *                    SubsService.GET_USING = 2 代表使用的，
	 *                    SubsService.GET_ALL = 3 代表全部.
	 * @param status      类型状态
	 *                    STATUS_ALL    X - 所有类型
	 *                    STATUS_HIDDEN    0 - 不展示给用户，即用户不可订阅
	 *                    STATUS_USER    1 - 展示给用户，用户可以订阅
	 * @return 消息小类的List
	 */
	public List<MsgSubCat> getMsgSubCatByCategory(MsgCategory msgCategory, char usingStatus, char status) {
		if (msgCategory == null) {
			return null;
		}
		Condition condition = new Condition();
		setUsingStatusCondition(condition, usingStatus, status);

		condition.add("msgCategory", msgCategory.getMsgCategory());
		OrderPart orders = new OrderPart();
		orders.add("sortIndex", OrderPart.ASC);
		List<MsgSubCat> result = genericDao.getByCondition(MsgSubCat.class, condition, orders);
		return result;
	}

	/**
	 * 根据usingStatus 配置condition
	 *
	 * @param condition
	 * @param usingStatus SubsService.GET_UNUSING = 1 代表没使用的，
	 *                    SubsService.GET_USING = 2 代表使用的，
	 *                    SubsService.GET_ALL = 3 代表全部.
	 * @param status      类型状态
	 *                    STATUS_ALL    X - 所有类型
	 *                    STATUS_HIDDEN    0 - 不展示给用户，即用户不可订阅
	 *                    STATUS_USER    1 - 展示给用户，用户可以订阅
	 * @return 配置好的condition
	 */
	private Condition setUsingStatusCondition(Condition condition, char usingStatus, char status) {
		if (condition == null) {
			condition = new Condition();
		}
		if (usingStatus == GET_UNUSING) {
			condition.add("isUse", "=", 1);
		} else if (usingStatus == GET_USING) {
			condition.add("isUse", "=", 0);
		} else if (usingStatus == GET_ALL) {
			//为空 不对"isUse" 进行条件判断
		}

		if (status == STATUS_ALL) {
			//因为现在只有2个类别 所以 不进行条件判断
		} else if (status == STATUS_HIDDEN) {       //不展示给用户的有哪些
			condition.add("status", "=", 0);
		} else if (status == STATUS_USER) {
			condition.add("status", "=", 1);
		}

		return condition;
	}

	//http://www.cnblogs.com/xiepeixing/archive/2012/11/13/2767733.html
	/// <summary>
	/// 过滤SQL语句,防止注入
	/// </summary>
	/// <param name="strSql"></param>
	/// <returns>0 - 没有注入, 1 - 有注入 </returns>
	public static int filterSql(String sSql) {
		int srcLen, decLen = 0;
		sSql = sSql.toLowerCase().trim();
		srcLen = sSql.length();
		sSql = sSql.replace("exec", "");
		sSql = sSql.replace("delete", "");
		sSql = sSql.replace("master", "");
		sSql = sSql.replace("truncate", "");
		sSql = sSql.replace("declare", "");
		sSql = sSql.replace("create", "");
		sSql = sSql.replace("xp_", "no");
		decLen = sSql.length();
		if (srcLen == decLen) return 0;
		else return 1;
	}
}
